﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Ledict.ConditionExtract.Common;
using Ledict.ConditionExtract.IDAL;
using Ledict.Core.Data;

namespace Ledict.ConditionExtract.DAL
{
    /// <summary>
    ///     创建索引数据处理类
    /// </summary>
    public class CreateIndexDal : ICreateIndexDal
    {
        /// <summary>
        ///     该方法暂时没用
        /// </summary>
        /// <param name="cateGoryIds"></param>
        /// <returns></returns>
        public DataTable GetIndexByCateGoryId(IList<string> cateGoryIds)
        {
            var strSql = new StringBuilder();
            strSql.Append(@"
//            select b.CaseId ,b.PropertyId,b.JudicialProcedureId  ,b.CourtId    from dbo.t_Case as b 
//             where  1 = 1 ");
            //            if (cateGoryIds != null && cateGoryIds.Any())
            //            {
            //                strSql.Append(cateGoryIds.ToJson())
            //            }
            //foreach (var item in HeadWords)
            //{
            //    strSql.Append(@" or d.HW_AllParentIDs like  '" + item + "%'  ");
            //}
            //strSql.Append(@")  ");

            var oBase = DatabaseFactory.CreateDatabase("Caselibrary");
            var oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            var oSet = oBase.ExecuteDataSet(oCmd);
            if (oSet.Tables.Count > 0)
            {
                return oSet.Tables[0];
            }
            return null;
        }


        /// <summary>
        ///     得到建立检索的所需的数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetIndex(IList<string> headWords)
        {
            var strSql = new StringBuilder();
            strSql.Append(@"
            select top 5000 b.CaseId , b. PropertyId, b.JudicialProcedureId  ,b.CourtId, e.FieldValue from dbo.t_Case as b 
            left join  dbo.t_Case_HeadWords_Associate as c
            on  c.CaseId=b.CaseId
            left join  dbo.t_Case_HeadWords  as d 
            on c.HeadWordId=d.Id
            left join dbo.t_Case_AdditionalFields e 
            on b.CaseId = e.CaseId and e.FieldId = '1100'
            where   
             Is_Index=0 and (1=0 
            ");
            foreach (var item in headWords)
            {
                strSql.Append(@" or d.HW_AllParentIDs like  '" + item + "%'  ");
            }
            strSql.Append(@")  ");

            var oBase = DatabaseFactory.CreateDatabase("Caselibrary");
            var oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            var oSet = oBase.ExecuteDataSet(oCmd);
            if (oSet.Tables.Count > 0)
            {
                return oSet.Tables[0];
            }
            return null;
        }

        /// <summary>
        ///     取出本次数据总数
        /// </summary>
        /// <param name="headWords"></param>
        /// <returns></returns>
        public int GetSumCount(IList<string> headWords)
        {
            var strSql = new StringBuilder();
            strSql.Append(@"
            select   count(distinct b.CaseId) from dbo.t_Case as b 
            left join  dbo.t_Case_HeadWords_Associate as c
            on  c.CaseId=b.CaseId
            left join  dbo.t_Case_HeadWords  as d 
            on c.HeadWordId=d.Id
            left join dbo.t_Case_AdditionalFields e 
            on b.CaseId = e.CaseId and e.FieldId = '1100'
            where   
             Is_Index=0 and (1=0 
            ");
            foreach (var item in headWords)
            {
                strSql.Append(@" or d.HW_AllParentIDs like  '" + item + "%'  ");
            }
            strSql.Append(@")  ");

            var oBase = DatabaseFactory.CreateDatabase("Caselibrary");
            var oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            return Convert.ToInt32(oBase.ExecuteScalar(oCmd));
        }

        /// <summary>
        ///     取出该案例ID关联的所有案由
        /// </summary>
        /// <param name="caseId">案例ID</param>
        /// <returns></returns>
        public DataTable GetHeadWords(string caseId)
        {
            var strSql = new StringBuilder();
            strSql.Append(@"
            select    (select  HW_AllParentIDs  from dbo.t_Case_HeadWords where  Id = a. HeadWordId)as HW_AllParentIDs 
            from dbo.t_Case_HeadWords_Associate as a 
            where CaseId=@CaseId 
");
            var oBase = DatabaseFactory.CreateDatabase("Caselibrary");
            var oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@CaseId", DbType.String, caseId);
            var oSet = oBase.ExecuteDataSet(oCmd);

            if (oSet.Tables.Count > 0)
            {
                return oSet.Tables[0];
            }
            return null;
        }

        /// <summary>
        ///     取逻辑段的文本内容
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetParagraphText(string id)
        {
            var strSql = new StringBuilder();
            strSql.Append(@"
            select ParagraphText from  t_Case_ParagraphLogic where id=@id
");
            var oBase = DatabaseFactory.CreateDatabase("Caselibrary");
            var oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@id", DbType.String, id);
            return LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd));
        }

        /// <summary>
        ///     取该案例的所有逻辑段(140,150)
        /// </summary>
        /// <param name="caseId">案例ID</param>
        /// <returns></returns>
        public DataTable GetParagraph(string caseId)
        {
            var strSql = new StringBuilder();
            strSql.Append(@" select ParagraphText ,ParagraphTypeId,id,caseId  from  t_Case_ParagraphLogic where CaseId=@CaseId
            and ParagraphTypeId in (120,140,150)");
            var oBase = DatabaseFactory.CreateDatabase("Caselibrary");
            var oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@CaseId", DbType.String, caseId);
            var oSet = oBase.ExecuteDataSet(oCmd);
            if (oSet.Tables.Count > 0)
            {
                return oSet.Tables[0];
            }
            return null;
        }

        /// <summary>
        ///     添加索引后更改状态
        /// </summary>
        /// <param name="caseId">案例ID</param>
        /// <returns></returns>
        public bool UpdateIndexStatus(int caseId)
        {
            var oBase = DatabaseFactory.CreateDatabase("Caselibrary");
            var oCmd = oBase.GetSqlStringCommand("Update t_Case set Is_Index=1 where CaseId=@CaseId");
            oBase.AddInParameter(oCmd, "@CaseId", DbType.Int32, caseId);
            var reValue = oBase.ExecuteNonQuery(oCmd);
            if (reValue > 0)
            {
                return true;
            }
            return false;
        }

        #region 案例相关 add by zhangpeng

        /// <summary>
        ///     根据案例类别，取出本次数据总数
        /// </summary>
        /// <param name="cateGoryId">案例类别</param>
        /// <returns></returns>
        public int GetCaseSumCount(int cateGoryId)
        {
            var strSql = new StringBuilder();
            strSql.Append(
                string.Format(@" select count(CaseId) from [caselibrary].[dbo].[t_Case] where Is_Index=0  and CateGoryId = {0}   
             ", cateGoryId));


            var oBase = DatabaseFactory.CreateDatabase("Caselibrary");
            var oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            return Convert.ToInt32(oBase.ExecuteScalar(oCmd));
        }

        /// <summary>
        ///     得到建立检索的所需的数据
        /// </summary>
        /// <param name="cateGoryId">案例类别</param>
        /// <returns></returns>
        public DataTable GetCaseIndex(int cateGoryId)
        {
            var strSql = new StringBuilder();
            strSql.Append(string.Format(@" select top 5000 b.CaseId ,b.PropertyId, b.JudicialProcedureId,b.CourtId 
                                           from dbo.t_Case as b 
                                            where Is_Index=0 and  CateGoryId = {0}", cateGoryId));


            var oBase = DatabaseFactory.CreateDatabase("Caselibrary");
            var oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            var oSet = oBase.ExecuteDataSet(oCmd);
            if (oSet != null && oSet.Tables.Count > 0)
            {
                return oSet.Tables[0];
            }
            return null;
        }

        /// <summary>
        /// 根据案例ID和逻辑段类型ID，查询逻辑段内容
        /// </summary>
        /// <param name="caseId">案例ID</param>
        /// <param name="paragraphTypeId">逻辑段类型ID</param>
        /// <returns></returns>
        public DataTable GetCaseParagraphLogic(int caseId, int paragraphTypeId)
        {
            var strSql = new StringBuilder();
            strSql.Append(string.Format(@" SELECT top 1 [Id]
                                          ,[CaseId]
                                          ,[ParagraphTypeId]
                                          ,[ParagraphText]
                                          ,[Is_Index]
                                      FROM [t_Case_ParagraphLogic]
                                      where caseid = {0} and ParagraphTypeId = {1}
                                        ", caseId, paragraphTypeId));


            var oBase = DatabaseFactory.CreateDatabase("Caselibrary");
            var oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            var oSet = oBase.ExecuteDataSet(oCmd);
            if (oSet != null && oSet.Tables.Count > 0)
            {
                return oSet.Tables[0];
            }
            return null;
        }


        #endregion
    }
}